﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using BCCommon;
using BCCommon.TMS.Driver;
using BCEntity.TMS.LearningTrain.PendingLearningVehicle;
using BCEntity.TMS.LearnTrain;
using BCEntity.TMS.Driver.DriverExamine;
using BCEntity.TMS.LearnTrain.LearnTrainStatistics;
using BCCommon.TMS.LearnTrain;
using BCEntity.TMS.LearningTrain.LearnTrainStatistics;

namespace BCData.TMS.LearningTrain
{
    /// <summary>
    /// 待培训骑手
    /// </summary>
    public class PendingLearningVehicleData : IPendingLearningVehicleData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		    PendingLearningVehicleId,
		    CompanyId,
		    DriverId,
		    IsNotifyPayment,
		    NotifyTime,
		    IsAbsenteeism,
		    IsOnceAbsenteeism,
		    VehicleTrainingScheduleRecordId,
		    CreateTime
        FROM
            PendingLearningVehicle ";
        private const string QUERY_ALLOWED_EXAMINATION_RECORD = @"SELECT AllowedExaminationRecordId,AllowedExaminationRecordCode,
            VehicleTrainingSchedulerecordId,LimitDuration,CreateTime,CreateAdminId,CreateAdminName FROM allowedexaminationrecord";

        public const string SQL_QUERY_PENDING = @" SELECT
                                A.PendingLearningVehicleId,
                                A.CompanyId,
                                A.DriverId,
                                A.IsNotifyPayment,
                                A.NotifyTime,
                                A.IsAbsenteeism,
                                A.IsOnceAbsenteeism,
                                A.VehicleTrainingScheduleRecordId,
                                A.CreateTime,
                                B.CompanyName,
                                C.Name AS DriverName,
                                C.VehicleNo,
                                C.Role,
                                E.Phone,
                                D.TrainingSiteConfigId,
                                D.TrainingSiteName,
                                D.TrainingSiteAddress,
                                D.TrainingStartDate,
                                D.TrainingEndDate,
                                D.TrainingTeacher,
                                D.TrainingTitle,
                                D.TrainingPeriods
                            FROM
                                PendingLearningVehicle A
                            INNER JOIN company B ON A.CompanyId=B.CompanyId
                            INNER JOIN driver C ON A.DriverId=C.DriverId
                            LEFT JOIN VehicleTrainingScheduleRecord D ON A.VehicleTrainingScheduleRecordId=D.VehicleTrainingScheduleRecordId
 							INNER JOIN user E ON E.userId =C.UserId  ";

        #region 构造函数
        public PendingLearningVehicleData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新待培训骑手纪录
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<PendingLearningVehicleEntity> InsertAsync(PendingLearningVehicleEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }
        #endregion

        #region updateAsync
        /// <summary>
        /// 修改待培训骑手记录
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<PendingLearningVehicleEntity> UpdateAsync(PendingLearningVehicleEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }
        #endregion 

        #region Delete
        /// <summary>
        /// 删除待培训骑手记录
        /// </summary>
        /// <param name="pendingLearningVehicleId"></param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(long pendingLearningVehicleId)
        {
            string sql = "DELETE FROM PendingLearningVehicle WHERE PendingLearningVehicleId = ?pendingLearningVehicleId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PendingLearningVehicleId", pendingLearningVehicleId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction);
        }
        #endregion

        #region GetById
        /// <summary>
        /// 获取待培训骑手记录
        /// </summary>
        /// <param name="pendingLearningVehicleId"></param>
        /// <returns></returns>
        public async Task<PendingLearningVehicleEntity> GetEntityAsync(long pendingLearningVehicleId)
        {
            string sql = QUERY + " WHERE  PendingLearningVehicleId = ?pendingLearningVehicleId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PendingLearningVehicleId", pendingLearningVehicleId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<PendingLearningVehicleEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询待培训骑手记录
        public async Task<(IEnumerable<PendingLearningVehicleEntity>, int)> GetListAsync(int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();


            string sql = string.Format(@"{0} {1} ; SELECT COUNT(1) FROM  PendingLearningVehicle  {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize
                  );

            return await this.database.Slave.QueryPageableListAsync<PendingLearningVehicleEntity>(sql, parameters);
        }


        #endregion

        public async Task<(IEnumerable<PendingLearningVehicleViewEntity>, int)> Query(long agentRecordId, string driverName, string vehicleNo,
            DriverRoleType? driverRoleType, bool? isTrainingScheduleRecord, DateTime? notifyStartDate, DateTime? notifyEndDate,
            int pageIndex, int pageSize)
        {
            DynamicParameters parameters = new DynamicParameters();

            //查询条件
            List<string> wheres = new List<string>
            {
                //" D.IsPendingTrain=1 ", //是否待培训
                //" D.IsPaidTrainingFee=1 ",//是否已缴纳培训费
                " C.IsLearnTrainPassed=0 ",//是否已考试成功
                " C.AgentRecordId=?AgentRecordId "
            };
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int32);
            if (!string.IsNullOrWhiteSpace(driverName))
            {
                wheres.Add(" C.Name  LIKE ?Name ");
                parameters.Add("?Name", $"%{driverName}%", DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(vehicleNo))
            {
                wheres.Add(" C.VehicleNo = ?VehicleNo ");
                parameters.Add("?VehicleNo", vehicleNo, DbType.AnsiString);
            }
            if (driverRoleType.HasValue)
            {
                wheres.Add(" C.Role = ?Role ");
                parameters.Add("?Role", driverRoleType.Value.ToString(), DbType.AnsiString);
            }
            if (isTrainingScheduleRecord.HasValue)
            {
                if (isTrainingScheduleRecord.Value)
                {
                    wheres.Add(" A.VehicleTrainingScheduleRecordId IS NOT NULL ");
                }
                else
                {
                    wheres.Add(" A.VehicleTrainingScheduleRecordId IS NULL ");
                }
            }
            if (notifyStartDate.HasValue)
            {
                wheres.Add(" A.NotifyTime >= ?NotifyStartDate ");
                parameters.Add("?NotifyStartDate", notifyStartDate.Value.Date, DbType.Date);
            }
            if (notifyEndDate.HasValue)
            {
                wheres.Add(" A.NotifyTime < ?NotifyEndDate ");
                parameters.Add("?NotifyEndDate", notifyEndDate.Value.AddDays(1).Date, DbType.Date);
            }

            var sqlWhere = wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "";
            var sql = $@" {SQL_QUERY_PENDING} {sqlWhere} ORDER BY A.PendingLearningVehicleId DESC LIMIT {(pageIndex - 1) * pageSize}, {pageSize};
                            SELECT COUNT(1) FROM  PendingLearningVehicle A                        
                            INNER JOIN company B ON A.CompanyId=B.CompanyId
                            INNER JOIN driver C ON A.DriverId=C.DriverId
                            LEFT JOIN VehicleTrainingScheduleRecord D ON A.VehicleTrainingScheduleRecordId=D.VehicleTrainingScheduleRecordId  
						    INNER JOIN user E ON E.userId =C.UserId  {sqlWhere}";
            return await this.database.Slave.QueryPageableListAsync<PendingLearningVehicleViewEntity>(sql, parameters);
        }

        public async Task<int> GetCount(long vehicleTrainingScheduleRecordId)
        {
            var sql = @" SELECT COUNT(PendingLearningVehicleId) FROM PendingLearningVehicle WHERE VehicleTrainingScheduleRecordId=?VehicleTrainingScheduleRecordId
                        AND IsAbsenteeism=0 ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<int>(sql, new { vehicleTrainingScheduleRecordId });
        }



        public async Task<VehicleTrainingScheduleStatisticsEntity> GetTrainingScheduleStatistics(long vehicleTrainingScheduleRecordId)
        {
            var sqlQuery = @" SELECT 
                                    B.VehicleTrainingScheduleRecordId,
                                    B.TrainingPeriods,
                                    B.TrainingTeacher,
                                    B.AppointmentAvailable,
                                    C.IsAllowedExamination,
                                    C.AllowedExaminationTime,
                                    COUNT(A.PendingLearningVehicleId) AS PendingLearningCount, -- 实际预约人数
                                    SUM(IF(A.IsAbsenteeism = 0,1,0)) AS PracticalLearningCount, -- 实际培训人数
                                    SUM(IF(A.IsAbsenteeism = 1,1,0)) AS AlreadyLearningCount, -- 缺考人数
                                    SUM(IF(C.IsLearnTrainPassed = 1,1,0)) AS PassDriverCount, -- 考试通过人数
                                    SUM(IF(C.IsLearnTrainPassed = 1 AND C.TrainExamFailedTimes = 0, 1, 0)) AS FirstPassDriverCount, -- 首次通过人数
                                    SUM(IF(C.IsLearnTrainPassed = 1 AND C.TrainExamFailedTimes = 1, 1, 0)) AS FirstMakeupPassDriverCount, -- 首次补考通过人数
                                    SUM(IF(C.IsLearnTrainPassed = 1 AND C.TrainExamFailedTimes = 2, 1, 0)) AS TwiceMakeupPassDriverCount, -- 二次补考通过人数
                                    SUM(IF(C.IsLearnTrainPassed = 0 AND C.TrainExamFailedTimes > 0, 1, 0)) AS NoPassDriverCount -- 未通过人数
                                     FROM PendingLearningVehicle A
                                    INNER JOIN VehicleTrainingScheduleRecord B ON A.VehicleTrainingScheduleRecordId=B.VehicleTrainingScheduleRecordId
                                    INNER JOIN driver C ON A.DriverId=C.DriverId
                                    WHERE B.VehicleTrainingScheduleRecordId=?VehicleTrainingScheduleRecordId; ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<VehicleTrainingScheduleStatisticsEntity>(sqlQuery, new { vehicleTrainingScheduleRecordId });

        }

        public async Task<IEnumerable<VehicleTrainingScheduleDetailEntity>> GetVehicleTrainingScheduleDetail(long agentRecordId, long vehicleTrainingScheduleRecordId,
            string phone)
        {
            var sqlQuery = @" SELECT 
	                            A.DriverId,B.PendingLearningVehicleId,C.VehicleTrainingScheduleRecordId,D.UserId,
                                D.UserName AS DriverName,D.Phone,DATE_FORMAT(C.TrainingStartDate,'%Y%m%d') AS TrainingStartDate,
                                A.IsLearnTrainPassed,A.IsLearnTrainSign,
                                (SELECT Mark FROM DriverExamineMark WHERE DriverId=A.DriverId ORDER BY CreateTime LIMIT 1) AS DriverExamineOne,
	                            (SELECT Mark FROM DriverExamineMark WHERE DriverId=A.DriverId ORDER BY CreateTime LIMIT 1,1) AS DriverExamineTwo,
	                            (SELECT Mark FROM DriverExamineMark WHERE DriverId=A.DriverId ORDER BY CreateTime LIMIT 2,1) AS DriverExamineThree,
	                            B.IsAbsenteeism,A.AllowedExaminationTime,A.IsAllowedExamination,
                                IF(G.ExamineCount IS NULL,0,G.ExamineCount) AS ExamineCount
                            FROM driver A
                            INNER JOIN PendingLearningVehicle B ON A.DriverId=B.DriverId
                            INNER JOIN VehicleTrainingScheduleRecord C ON C.VehicleTrainingScheduleRecordId =B.VehicleTrainingScheduleRecordId
                            INNER JOIN User D ON D.UserId=A.UserId 
                            LEFT JOIN (SELECT DriverId,COUNT(DriverId) AS ExamineCount FROM DriverExamineMark GROUP BY DriverId) G ON G.DriverId=A.DriverId  ";
            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();
            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add(" D.Phone = ?Phone ");
                parameters.Add("?Phone", phone, DbType.AnsiString);
            }
            wheres.Add(" C.AgentRecordId = ?AgentRecordId ");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            wheres.Add(" C.VehicleTrainingScheduleRecordId = ?VehicleTrainingScheduleRecordId ");
            parameters.Add("?VehicleTrainingScheduleRecordId", vehicleTrainingScheduleRecordId, DbType.Int64);
            var sqlWhere = wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "";
            var sql = $"{sqlQuery} {sqlWhere}";
            return await this.database.Slave.QueryAsync<VehicleTrainingScheduleDetailEntity>(sql, parameters);
        }

        public async Task<IEnumerable<PendingLearningVehicleEntity>> GetListByIdsAsync(List<long> pendingLearningVehicleIds)
        {
            string sql = string.Format(@"SELECT A.PendingLearningVehicleId,A.DriverId,A.CompanyId,A.IsNotifyPayment,A.NotifyTime,A.IsAbsenteeism,A.IsOnceAbsenteeism,
                                           A.VehicleTrainingScheduleRecordId FROM PendingLearningVehicle A
                                            INNER JOIN driver B ON A.DriverId = B.DriverId
                                         WHERE FIND_IN_SET(A.PendingLearningVehicleId,?PendingLearningVehicleId) AND A.IsAbsenteeism = 0 AND B.IsAllowedExamination=0
                                        AND B.AllowedExaminationTime IS NULL AND B.TrainExamFailedTimes = 0 AND B.MakeUpTimes=0 ;", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PendingLearningVehicleId", string.Join(",", pendingLearningVehicleIds), DbType.AnsiString);
            return await this.database.Slave.QueryAsync<PendingLearningVehicleEntity>(sql, parameters);
        }

        /// <summary>
        /// 允许考试
        /// </summary>
        /// <param name="driverIds"></param>
        /// <param name="vehicleTrainingScheduleRecordId">培训安排id</param>
        /// <param name="limitDuration">考试限制时长</param>
        /// <returns></returns>
        public async Task<bool> AllowJoinExamAsync(List<long> driverIds, long vehicleTrainingScheduleRecordId, int limitDuration)
        {
            string sql = @"UPDATE Driver SET IsAllowedExamination=?IsAllowedExamination,AllowedExaminationTime=?AllowedExaminationTime,LimitDuration=?LimitDuration
                         WHERE FIND_IN_SET(DriverId,?DriverIds) AND AllowedExaminationTime IS NULL AND IsAllowedExamination=0 AND TrainExamFailedTimes = 0 AND MakeUpTimes=0; 
                       UPDATE VehicleTrainingScheduleRecord SET IsAllowedExamination=?IsAllowedExamination,AllowedExaminationTime=?AllowedExaminationTime 
                        WHERE VehicleTrainingScheduleRecordId=?VehicleTrainingScheduleRecordId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?IsAllowedExamination", true, DbType.Boolean);
            parameters.Add("?IsTrained", true, DbType.Boolean);
            parameters.Add("?DriverIds", string.Join(",", driverIds), DbType.String);
            parameters.Add("?AllowedExaminationTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?VehicleTrainingScheduleRecordId", vehicleTrainingScheduleRecordId, DbType.Int64, size: 20);
            parameters.Add("?LimitDuration", limitDuration, DbType.Int64, size: 20);
            return await this.database.Master.ExecuteAsync(sql, parameters) > 0;
        }


        #region 进入考场
        /// <summary>
        /// 考场统计
        /// </summary>
        /// <param name="vehicleTrainingScheduleRecordId"></param>
        /// <returns></returns>
        public async Task<ExaminationStatisticsEntity> GetExaminationStatistics(long? vehicleTrainingScheduleRecordId)
        {
            string sqlTemp = @"SELECT 
                            A.VehicleTrainingScheduleRecordId,
                            B.TrainingPeriods,
                            B.TrainingTeacher,
					        B.TrainingTitle,
                            SUM(IF(A.IsAbsenteeism = 0 && C.AllowedExaminationTime IS NOT NULL,1,0)) AS PracticalLearningCount -- 本次考试人数
                            FROM PendingLearningVehicle A 
                            INNER JOIN VehicleTrainingScheduleRecord B 
                                ON A.VehicleTrainingScheduleRecordId = B.VehicleTrainingScheduleRecordId 
                            INNER JOIN Driver C ON A.DriverId = C.DriverId  ";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (vehicleTrainingScheduleRecordId.HasValue)
            {
                wheres.Add(" A.VehicleTrainingScheduleRecordId = ?VehicleTrainingScheduleRecordId ");
                parameters.Add("?VehicleTrainingScheduleRecordId", vehicleTrainingScheduleRecordId, DbType.Int64);
            }
            string sql = string.Format("{0} {1}",
                sqlTemp, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");

            return await this.database.Slave.QueryFirstOrDefaultAsync<ExaminationStatisticsEntity>(sql, parameters);
        }
        /// <summary>
        /// 获取累计培训人员
        /// </summary>
        /// <returns></returns>
        public async Task<ExaminationStatisticsEntity> GetAllTrainCount()
        {
            string sql = @" SELECT COUNT(PendingLearningVehicleId) AS AllPracticalLearningCount FROM PendingLearningVehicle A
                            INNER JOIN VehicleTrainingScheduleRecord B ON A.VehicleTrainingScheduleRecordId=B.VehicleTrainingScheduleRecordId
                            WHERE A.IsAbsenteeism=0";
            return await this.database.Slave.QueryFirstOrDefaultAsync<ExaminationStatisticsEntity>(sql);
        }
        /// <summary>
        /// 获取最老的考试记录时间
        /// </summary>
        /// <param name="vehicleTrainingScheduleRecordId"></param>
        /// <returns></returns>
        public async Task<LearnTrainAllowedExaminationRecordEntity> GetLearnTrainAllowedExaminationRecordEntity(long vehicleTrainingScheduleRecordId)
        {
            string sql =
                $"{QUERY_ALLOWED_EXAMINATION_RECORD} WHERE VehicleTrainingScheduleRecordId=?VehicleTrainingScheduleRecordId ORDER BY CreateTime LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTrainingScheduleRecordId", vehicleTrainingScheduleRecordId, DbType.Int64);
            return await this.database.Slave.QueryFirstOrDefaultAsync<LearnTrainAllowedExaminationRecordEntity>(sql, parameters);
        }

        /// <summary>
        /// 获取考试通过和未通过司机信息
        /// </summary>
        /// <param name="vehicleTrainingScheduleRecordId">培训安排id</param>
        /// <returns></returns>
        public async Task<IEnumerable<LearnTrainExaminationDriverEntity>> GetExaminationDriverInfo(long vehicleTrainingScheduleRecordId, bool IsLearnTrainPassed)
        {
            DynamicParameters parameters = new DynamicParameters();
            string where = "";
            if (IsLearnTrainPassed)//考试通过
            {
                where = " AND D.IsLearnTrainPassed=1 ";
            }
            else
            {
                where = " AND D.IsLearnTrainPassed=0 AND D.IsAllowedExamination=0 ";
            }
            parameters.Add("?VehicleTrainingScheduleRecordId", vehicleTrainingScheduleRecordId, DbType.Int64);

            string sql = string.Format(@" SELECT
	                                        B.PendingLearningVehicleId,
	                                        B.IsAbsenteeism,
	                                        C.TrainingPeriods,
	                                        C.TrainingTeacher,
	                                        C.TrainingTitle,
	                                        A.DriverId,
	                                        D.UserId,
	                                        E.UserName,
	                                        A.Mark AS DriverExamine,
	                                        A.CreateTime AS SubmitExaminationTime,
	                                        D.AllowedExaminationTime
                                        FROM
	                                        DriverExamineMark A
                                        INNER JOIN PendingLearningVehicle B ON A.DriverId = B.DriverId
                                        INNER JOIN VehicleTrainingScheduleRecord C ON B.VehicleTrainingScheduleRecordId = C.VehicleTrainingScheduleRecordId AND C.VehicleTrainingScheduleRecordId=?VehicleTrainingScheduleRecordId
                                        INNER JOIN Driver D ON A.DriverId = D.DriverId
                                        INNER JOIN `User` E ON D.UserId = E.UserId
                                        WHERE A.ExamineMarkId IN (SELECT SUBSTRING_INDEX(GROUP_CONCAT(ExamineMarkId ORDER BY CreateTime DESC),',',1) FROM DriverExamineMark
                                        GROUP BY DriverId ) {0}  ORDER BY A.CreateTime DESC ", where);
            return await this.database.Slave.QueryAsync<LearnTrainExaminationDriverEntity>(sql, parameters);
        }
        /// <summary>
        /// 获取考试中司机信息
        /// </summary>
        /// <param name="vehicleTrainingScheduleRecordId"></param>
        /// <returns></returns>
        public async Task<IEnumerable<LearnTrainExaminationDriverEntity>> GetExaminationHaving(long vehicleTrainingScheduleRecordId)
        {
            string sql = @"  SELECT A.IsAbsenteeism, A.PendingLearningVehicleId, B.VehicleTrainingScheduleRecordId
                                                , C.DriverId, D.UserId, D.UserName, C.IsLearnTrainPassed, C.IsAllowedExamination
                                                , B.TrainingPeriods, B.TrainingTeacher, B.TrainingTitle
                                                , C.AllowedExaminationTime, C.LimitDuration,C.TrainExamFailedTimes
                                                FROM PendingLearningVehicle A
                                                INNER JOIN VehicleTrainingScheduleRecord B ON A.VehicleTrainingScheduleRecordId = B.VehicleTrainingScheduleRecordId
                                                INNER JOIN driver C ON C.DriverId = A.DriverId
                                                INNER JOIN `user` D ON D.UserId = C.UserId
                                                WHERE A.IsAbsenteeism = 0  AND A.VehicleTrainingScheduleRecordId = ?VehicleTrainingScheduleRecordId
                                                 AND C.IsLearnTrainPassed = 0  AND C.IsAllowedExamination = 1  AND C.AllowedExaminationTime IS NOT NULL ;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTrainingScheduleRecordId", vehicleTrainingScheduleRecordId, DbType.Int64);
            return await this.database.Slave.QueryAsync<LearnTrainExaminationDriverEntity>(sql, parameters);
        }
        public async Task<DriverExamineMarkEntity> GetDriverExamine(long driverId)
        {
            string sql = @" SELECT ExamineMarkId FROM DriverExamineMark WHERE DriverId=?DriverId ";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?DriverId", driverId, DbType.Int64);
            return await this.database.Slave.QueryFirstOrDefaultAsync<DriverExamineMarkEntity>(sql, parameter);
        }

        public async Task<IEnumerable<LearnTrainExaminationDriverEntity>> GetNoExaminationHaving(long vehicleTrainingScheduleRecordId)
        {
            string sql = @"  SELECT A.IsAbsenteeism, A.PendingLearningVehicleId, B.VehicleTrainingScheduleRecordId
                                                , C.DriverId, D.UserId, D.UserName, C.IsLearnTrainPassed, C.IsAllowedExamination
                                                , B.TrainingPeriods, B.TrainingTeacher, B.TrainingTitle
                                                , C.AllowedExaminationTime, C.LimitDuration,C.TrainExamFailedTimes
                                                FROM PendingLearningVehicle A
                                                INNER JOIN VehicleTrainingScheduleRecord B ON A.VehicleTrainingScheduleRecordId = B.VehicleTrainingScheduleRecordId
                                                INNER JOIN driver C ON C.DriverId = A.DriverId
                                                INNER JOIN `user` D ON D.UserId = C.UserId
                                                WHERE A.IsAbsenteeism = 0  AND A.VehicleTrainingScheduleRecordId = ?VehicleTrainingScheduleRecordId
                                                 AND C.IsLearnTrainPassed = 0  AND C.IsAllowedExamination = 1  AND C.AllowedExaminationTime IS NOT NULL 
                                                    AND NOW()>DATE_ADD(C.AllowedExaminationTime,INTERVAL LimitDuration MINUTE) AND C.TrainExamFailedTimes>0 ;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTrainingScheduleRecordId", vehicleTrainingScheduleRecordId, DbType.Int64);
            return await this.database.Slave.QueryAsync<LearnTrainExaminationDriverEntity>(sql, parameters);
        }

        #endregion

        #region 统计

        /// <summary>
        /// 获取上月以及本月 应安排培训人数
        /// </summary>
        /// <param name="PartnerId"></param>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public async Task<CommonDriverEntity> GetLearningDriverCount(long? agentRecordId, DateTime? datetime, LearnTrainEnum LearnTrainEnum,
            long? trainingSiteConfigId)
        {
            List<string> wheres = new List<string>();
            string whereLearnTrainEnum = string.Empty;
            string sqlWhere = string.Empty;
            DynamicParameters parameters = new DynamicParameters();
            ;
            if (LearnTrainEnum == LearnTrainEnum.PendingLearning)//应安排培训人数
            {
                whereLearnTrainEnum = " ";
            }
            else if (LearnTrainEnum == LearnTrainEnum.PracticalLearning)//实际安排培训人数
            {
                whereLearnTrainEnum = " AND A.VehicleTrainingScheduleRecordId IS NOT NULL ";
            }
            else if (LearnTrainEnum == LearnTrainEnum.UnscheduledLearning)//未安排培训人数
            {
                whereLearnTrainEnum = " AND A.VehicleTrainingScheduleRecordId IS NULL ";
            }
            else if (LearnTrainEnum == LearnTrainEnum.ArrivalLearning)//实到培训人数
            {
                whereLearnTrainEnum = " AND A.VehicleTrainingScheduleRecordId IS NOT NULL AND A.IsAbsenteeism=0 ";
            }
            else if (LearnTrainEnum == LearnTrainEnum.AlreadyLearning)//已安排未培训人数
            {
                whereLearnTrainEnum = " AND A.VehicleTrainingScheduleRecordId IS NOT NULL AND A.IsAbsenteeism=1 ";
            }
            else if (LearnTrainEnum==LearnTrainEnum.PassLearning)
            {
                whereLearnTrainEnum = " AND C.IsLearnTrainPassed=1 ";
            }
            if (agentRecordId.HasValue)
            {
                wheres.Add(" B.AgentRecordId =?AgentRecordId ");
                parameters.Add("?AgentRecordId", agentRecordId, DbType.Int32);
            }
            if (trainingSiteConfigId.HasValue)
            {
                wheres.Add(" B.TrainingSiteConfigId =?TrainingSiteConfigId ");
                parameters.Add("?TrainingSiteConfigId", trainingSiteConfigId.Value, DbType.Int32);
            }
            datetime = datetime.HasValue ? datetime : DateTime.Now;
            parameters.Add("?DateTime", datetime, DbType.DateTime);
            parameters.Add("?MonthTime", datetime.Value.AddMonths(-1), DbType.DateTime);
            if (wheres.Count>0)
            {
                sqlWhere = $" AND {string.Join(" AND ", wheres)} ";
            }
            string sql = string.Format(@"SELECT
	                                        SUM( H.DriverCount ) AS DriverCount,
	                                        SUM( H.MonthDriverCount ) AS MonthDriverCount 
                                        FROM
	                                        (
	                                        SELECT
                                            COUNT(A.PendingLearningVehicleId) AS DriverCount,
                                            0 AS MonthDriverCount 
                                            FROM
                                            pendinglearningvehicle A
                                            INNER JOIN vehicletrainingschedulerecord B ON A.VehicleTrainingScheduleRecordId=B.VehicleTrainingScheduleRecordId
                                            INNER JOIN Driver C ON C.DriverId =A.DriverId                                            
                                            WHERE
                                            DATE_FORMAT( B.TrainingStartDate, '%Y%m' ) = DATE_FORMAT(?DateTime, '%Y%m' )
                                            {0}{1}
                                        UNION
	                                        SELECT
		                                        0 AS DriverCount,
		                                        COUNT(A.PendingLearningVehicleId) AS MonthDriverCount 
	                                        FROM
		                                        pendinglearningvehicle A 
                                                INNER JOIN vehicletrainingschedulerecord B ON A.VehicleTrainingScheduleRecordId=B.VehicleTrainingScheduleRecordId
                                                INNER JOIN Driver C ON C.DriverId =A.DriverId  
	                                        WHERE
		                                        DATE_FORMAT( B.TrainingStartDate, '%Y%m' ) = DATE_FORMAT(?MonthTime, '%Y%m' ) 
                                            {0}{1}
	                                        ) H", sqlWhere, whereLearnTrainEnum);
            return await this.database.Slave.QueryFirstOrDefaultAsync<CommonDriverEntity>(sql, parameters);
        }

        public async Task<IEnumerable<LearnTrainMonthAnalysisEntity>> GetTrainSeheduleAnalysisAsync(long agentRecordId, DateTime date)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add(" B.AgentRecordId =?AgentRecordId ");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            var currentMonthFirstDay = new DateTime(date.Year, date.Month, 1, 0, 0, 0);
            var nextMonthFirstDay = currentMonthFirstDay.AddMonths(1);

            wheres.Add("B.TrainingStartDate >= ?CurrentMonthFirstDay AND B.TrainingStartDate < ?NextMonthFirstDay");
            parameters.Add("?CurrentMonthFirstDay", currentMonthFirstDay, DbType.Date);
            parameters.Add("?NextMonthFirstDay", nextMonthFirstDay, DbType.Date);

            string sql = string.Format(@"SELECT '{0}' AS QueryDate,
                                        IF(B.TrainingPeriods IS NOT NULL AND B.TrainingPeriods <> '',B.TrainingPeriods,B.VehicleTrainingScheduleRecordId) AS TrainingPeriods,
                                        COUNT(A.DriverId) AS TrainCount, -- 培训人数
                                        SUM(IF(C.TrainExamFailedTimes = 1,1,0)) AS FirstMakeUpCount, -- 首次补考人数
                                        SUM(IF(C.TrainExamFailedTimes = 2,1,0)) AS SecondMakeUpCount, -- 二次补考人数
                                        SUM(IF(C.IsLearnTrainPassed = 1 AND C.TrainExamFailedTimes = 0,1,0)) AS FirstPassedCount, -- 首次通过人数
                                        SUM(IF(C.IsLearnTrainPassed = 1 AND C.TrainExamFailedTimes = 1,1,0)) AS SecondPassedCount, -- 二次通过人数
                                        SUM(IF(C.IsLearnTrainPassed = 0 AND C.TrainExamFailedTimes > 0,1,0)) AS NotPassedCount -- 未通过人数
                                        FROM PendingLearningVehicle A 
                                        INNER JOIN vehicletrainingschedulerecord B 
                                        ON A.VehicleTrainingScheduleRecordId = B.VehicleTrainingScheduleRecordId 
                                        LEFT JOIN Driver C ON A.DriverId = C.DriverId
                                        WHERE {1}
                                        GROUP BY IF(B.TrainingPeriods IS NOT NULL AND B.TrainingPeriods <> '',B.TrainingPeriods,B.VehicleTrainingScheduleRecordId)",
                                        currentMonthFirstDay.ToString("yyyyMM"), string.Join(" AND ", wheres));

            return await this.database.Slave.QueryAsync<LearnTrainMonthAnalysisEntity>(sql, parameters);
        }

        /// <summary>
        /// 获取考试人数
        /// </summary>
        /// <param name="agentRecordId"></param>
        /// <param name="Datetime"></param>
        /// <returns></returns>
        public async Task<ExamineDriverCountEntity> GetFirstExamDriverCount(long agentRecordId, DateTime? datetime)
        {
            string sqlTemp = @"SELECT SUM(IF(A.TrainExamFailedTimes=1,1,0)) AS FirstExamDriverCount, -- 首次补考人数 
								SUM(IF(A.TrainExamFailedTimes=2,1,0)) AS  TwiceExamDriverCount, -- 二次补考人数
								SUM(IF(A.IsLearnTrainPassed = 1,1,0)) AS FirstPassDriverCount, -- 首次通过人数
								SUM(IF(A.IsLearnTrainPassed = 1 AND A.TrainExamFailedTimes = 1,1,0)) AS FirstMakeupPassDriverCount, -- 首次补考通过人数
                                SUM(IF(A.IsLearnTrainPassed = 1 AND A.TrainExamFailedTimes = 2,1,0)) AS TwiceMakeupPassDriverCount, -- 二次补考通过人数
								SUM(IF(A.IsLearnTrainPassed = 0 AND A.TrainExamFailedTimes > 0,1,0)) AS NotPassDriverCount, -- 未通过人数
								SUM(IF(A.AllowedExaminationTime IS NOT NULL,1,0)) AS  AllExamDriverCount -- 总人数
								FROM driver A
								INNER JOIN `user` B ON A.UserId=B.UserId
                                INNER JOIN agentrecord C ON C.AgentRecordUuid=A.AgentRecordUuid
								WHERE DATE_FORMAT(A.AllowedExaminationTime,'%Y%m')=DATE_FORMAT(?Datetime,'%Y%m') {0} ";
        //    string sqlTemp = @"SELECT SUM(IF(A.TrainExamFailedTimes=1,1,0)) AS FirstExamDriverCount, -- 首次补考人数 
								//SUM(IF(A.TrainExamFailedTimes=2,1,0)) AS  TwiceExamDriverCount, -- 二次补考人数
								//SUM(IF(A.IsLearnTrainPassed = 1 AND A.TrainExamFailedTimes = 0,1,0)) AS FirstPassDriverCount, -- 首次通过人数
								//SUM(IF(A.IsLearnTrainPassed = 1 AND A.TrainExamFailedTimes = 1,1,0)) AS FirstMakeupPassDriverCount, -- 首次补考通过人数
        //                        SUM(IF(A.IsLearnTrainPassed = 1 AND A.TrainExamFailedTimes = 2,1,0)) AS TwiceMakeupPassDriverCount, -- 二次补考通过人数
								//SUM(IF(A.IsLearnTrainPassed = 0 AND A.TrainExamFailedTimes > 0,1,0)) AS NotPassDriverCount, -- 未通过人数
								//SUM(IF(A.AllowedExaminationTime IS NOT NULL,1,0)) AS  AllExamDriverCount -- 总人数
								//FROM driver A
								//INNER JOIN `user` B ON A.UserId=B.UserId
        //                        INNER JOIN agentrecord C ON C.AgentRecordUuid=A.AgentRecordUuid
								//WHERE DATE_FORMAT(A.AllowedExaminationTime,'%Y%m')=DATE_FORMAT(?Datetime,'%Y%m') {0} ";
            string where = " AND C.AgentRecordId =?AgentRecordId ";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int32);
            datetime = datetime.HasValue ? datetime : DateTime.Now;
            parameters.Add("?DateTime", datetime, DbType.DateTime);
            string sql = string.Format(sqlTemp, where);
            return await this.database.Slave.QueryFirstOrDefaultAsync<ExamineDriverCountEntity>(sql, parameters);
        }

        /// <summary>
        /// 获取培训详情列表
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<TrainCountEntity>> GetTrainCountDetailList(long agentRecordId)
        {
            string sql = @"SELECT COUNT(M.DriverId) AS TrainCount,M.TrainDate FROM 
                            (
                                SELECT  A.DriverId,DATE_FORMAT(C.CreateTime,'%Y-%m') AS TrainDate 
                                FROM driver A
                                INNER JOIN pendinglearningvehicle B ON A.DriverId=B.DriverId
                                INNER JOIN vehicletrainingschedulerecord C ON C.VehicleTrainingScheduleRecordId=B.VehicleTrainingScheduleRecordId
                                WHERE B.IsAbsenteeism=0 AND C.AgentRecordId=?AgentRecordId
                            ) M
                            GROUP BY M.TrainDate";
            return await this.database.Slave.QueryAsync<TrainCountEntity>(sql,new { agentRecordId });
        }

        public async Task<IEnumerable<PendingLearningVehicleEntity>> GetList(long vehicleTrainingScheduleRecordId)
        {
            var sql = $"{QUERY} WHERE VehicleTrainingScheduleRecordId=?VehicleTrainingScheduleRecordId";
            return await this.database.Slave.QueryAsync<PendingLearningVehicleEntity>(sql,
                new {vehicleTrainingScheduleRecordId});
        }

        #endregion

        /// <summary>
        /// 获取培训历史记录
        /// </summary>
        /// <param name="agentRecordId">代理商id</param>
        /// <param name="vehicleNo">司机编号</param>
        /// <param name="trainingSiteConfigId">培训点id</param>
        /// <param name="trainingStartDate">培训开始时间</param>
        /// <param name="trainingEndDate">培训结束时间</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public async Task<(IEnumerable<LearnTrainHistoryRecordEntity>, int)> GetLearnTrainHistoryRecordEntity(long agentRecordId,
            string vehicleNo, long? trainingSiteConfigId,DateTime? trainingStartDate, DateTime? trainingEndDate, int pageIndex, int pageSize)
        {
            #region
            string sqlItem = @"SELECT
                                A.DriverId,
                                B.UserId,
                                F.AgentRecordId,
                                F.VehicleTrainingScheduleRecordId,
                                E.PendingLearningVehicleId,
                                B.UserName AS DriverName,
                                A.VehicleNo,
                                B.Phone,
                                F.TrainingTitle,
                                F.TrainingPeriods,
                                F.TrainingSiteConfigId,
                                F.TrainingSiteAddress,
                                DATE_FORMAT( F.TrainingStartDate, '%Y-%m-%d' ) AS TrainingStartDate,
                                A.IsLearnTrainPassed,
                                G.DriverExamine,
                                IF
                                ( G.DriverExamineCount > 0 , 1, 0 ) AS IsDriverExamine 
                                FROM
                                driver A
                                INNER JOIN USER B ON A.UserId = B.UserId
                                INNER JOIN PendingLearningVehicle E ON E.DriverId = A.DriverId
                                INNER JOIN VehicleTrainingScheduleRecord F ON E.VehicleTrainingScheduleRecordId = F.VehicleTrainingScheduleRecordId
                                INNER JOIN ( SELECT DriverId, MAX( Mark ) AS DriverExamine, COUNT( DriverId ) AS DriverExamineCount FROM DriverExamineMark GROUP BY DriverId ) G
                                ON G.DriverId = A.DriverId ";
            string sqlCount = @"SELECT
                                A.DriverId
                                FROM
                                driver A
                                INNER JOIN USER B ON A.UserId = B.UserId
                                INNER JOIN PendingLearningVehicle E ON E.DriverId = A.DriverId
                                INNER JOIN VehicleTrainingScheduleRecord F ON E.VehicleTrainingScheduleRecordId = F.VehicleTrainingScheduleRecordId
                                INNER JOIN ( SELECT DriverId, MAX(Mark) AS DriverExamine, COUNT(DriverId) AS DriverExamineCount FROM DriverExamineMark GROUP BY DriverId ) G
                                ON G.DriverId = A.DriverId ";
            #endregion

            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add(" F.AgentRecordId=?AgentRecordId ");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int32);
            if (!string.IsNullOrWhiteSpace(vehicleNo))
            {
                wheres.Add(" A.VehicleNo=?VehicleNo ");
                parameters.Add("?VehicleNo", vehicleNo, DbType.AnsiString);
            }
            if (trainingSiteConfigId.HasValue)
            {
                wheres.Add(" F.TrainingSiteConfigId=?TrainingSiteConfigId ");
                parameters.Add("?TrainingSiteConfigId", trainingSiteConfigId.Value, DbType.Int32);
            }
            if (trainingStartDate.HasValue)
            {
                wheres.Add(" F.TrainingStartDate>=?TrainingStartDate ");
                parameters.Add("?TrainingStartDate", trainingStartDate.Value.Date, DbType.DateTime);
            }
            if (trainingEndDate.HasValue)
            {
                wheres.Add(" F.TrainingStartDate<?TrainingEndDate ");
                parameters.Add("?TrainingEndDate", trainingEndDate.Value.AddDays(1).Date, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} GROUP BY A.DriverId ORDER BY E.CreateTime DESC LIMIT {2},{3};
                                SELECT COUNT(1) FROM  ( {4} {1} GROUP BY A.DriverId) M",
          sqlItem,
          wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
          (pageIndex - 1) * pageSize,
          pageSize, sqlCount);
            return await this.database.Slave.QueryPageableListAsync<LearnTrainHistoryRecordEntity>(sql, parameters);
        }

        public async Task<PendingLearningVehicleEntity> GetEntityByDriverId(long driverId)
        {
            var sql = $" {QUERY} WHERE DriverId=?DriverId ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<PendingLearningVehicleEntity>(sql, new { driverId });
        }
    }
}



